package db.view;

import java.io.File;
import java.io.PrintStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DatabaseMetaData;
import java.sql.Statement;

import java.io.BufferedReader;
import java.io.BufferedWriter;

import java.io.FileInputStream;
import java.io.DataInputStream;
import java.io.InputStreamReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;


import java.util.Iterator;
import java.util.Properties;
import java.util.Vector;

public class SQLHtmlFormatter

{
	public static String tsql,tpartyid;
	
	
  public ResultSet executeSQL(Connection con, String sql)
    throws Exception
  {
    ResultSet rs = con.createStatement().executeQuery(sql);

    return rs;
  }
  
  
  public Vector<String> getSQLsFromFile(String fileName) throws Exception
  {
  
	Vector<String> sqls = new Vector<String>();
	DataInputStream in = null;
	try{
		// Open the file that is the first 
		// command line parameter
		FileInputStream fstream = new FileInputStream(fileName);
		// Get the object of DataInputStream
		in = new DataInputStream(fstream);
		BufferedReader br = new BufferedReader(new InputStreamReader(in));
		String strLine;
		//Read File Line By Line
		while ((strLine = br.readLine()) != null)   {
			// Print the content on the console
			if(strLine.startsWith("#") || strLine.trim().equals(""))
			{
			}
			else
				sqls.add(strLine);
		}
	
	}
	finally 
	{
		try
		{
			if(in != null )
			{
				in.close();
			}
		}
		catch(Exception e)
		{
		}						
	}
	return sqls;
  }

  public Connection getConnection(String dbConfigFile) throws Exception
  {
    Class.forName("oracle.jdbc.driver.OracleDriver");
	//  Class.forName("com.ibm.db2.jcc.DB2Driver");
	Properties prop = new Properties();
	prop.load(new FileInputStream("db.properties"));
    String user = prop.getProperty("UserName");
    String pass = prop.getProperty("Password");
    String url = prop.getProperty("DBURL");
	
    Connection con = 
      DriverManager.getConnection(url, user, pass);

    return con;
  }

  public void printResultSetAsHtml(PrintWriter pw, ResultSet rs, String sql) throws Exception
  {
    
	ResultSetMetaData md = rs.getMetaData();
    int count = md.getColumnCount();
    //String tableName = md.getTableName(1);
	String tempSql = sql.toUpperCase();
	int tbIdStart = tempSql.indexOf("FROM ");
	int tbIdEnd = tempSql.indexOf(" ", tbIdStart+5);
	
	String tableName = tempSql.substring(tbIdStart+5, tbIdEnd);
	
	
	
	
	//System.out.println("******TableName************" + tableName);

    pw.println("<table border=1 CLASS=\"boldtable\">");
    String tableHeading = "<tr><td colspan=\"" + count + "\" align=left><b><font color=\"green\" size=\"4\">" + tableName + "</font></b></td></tr>";
    pw.println(tableHeading);
    pw.println("<tr>");
    for (int i = 1; i <= count; i++) {
      pw.println("<th>");
      pw.println(md.getColumnLabel(i));
    }
    pw.println("</tr>");
    boolean dataPresent = false;
    while (rs.next()) {
      pw.println("<tr>");
      dataPresent = true;
      for (int i = 1; i <= count; i++) {
        pw.println("<td>");
		if(rs.getString(i) != null)
			pw.println(rs.getString(i));
		else
			pw.println("");
        pw.println("</td>");
      }
      pw.println("</tr>");
    }

    if (!dataPresent)
    {
      String emptyRow = "<tr> <td colspan=\"" + count + "\"><font color=\"red\">No Data Found</font></td></tr>";
      pw.println(emptyRow);
    }
    pw.println("</table><br/><br/>");
  }

  public static void main(String[] args)
  {
	  
	  
	  /*"C:\Users\QA Admin\Documents\bat\temp\sqltohtml\dbConfig.properties"
	  "C:\Users\QA Admin\Documents\bat\temp\sqltohtml\MDM.txt"
	  658036379647531473*/
    SQLHtmlFormatter shf = new SQLHtmlFormatter();

    if ((args != null) && (args.length != 3)) {
      System.err.println("Usage: java SQLHtmlFormatter <dbConfig.properties> <sqls.txt> <cont_id>");
      System.exit(-1);
    }

    String dbConfigFile = args[0];
    String sqlFile = args[1];
    String contId = args[2];
    Connection conn = null;
    ResultSet rs = null;
    File file = new File("AE_Output_" + contId + ".html");
    PrintWriter pw = null;
    try
    {
      pw = new PrintWriter(file);
      conn = shf.getConnection(dbConfigFile);
      String format = "<STYLE TYPE=\"text/css\"><!-- .boldtable, .boldtable TD, .boldtable TH \t    {\t    \tborder-style: solid; border-width: 1px; border-spacing: 0px; border-color:#999999; font-family:sans-serif;\t    \tfont-size:9pt;\t    \tcolor:blue;\t    \tbackground-color:#e0eee0;\t    \t} .myh1 { font-family:sans-serif;} --></STYLE>";
      pw.println(format);
      String summary = "<h1 class=\"myh1\">Summary for Party: " + contId + "</h1><br/>";
      pw.println(summary);
	  Vector<String> sqls = shf.getSQLsFromFile(sqlFile);
	  
      for (String sql : sqls)
      {
        sql = sql.replace("?", contId);

		System.out.println("Executing SQL " + sql);
        rs = shf.executeSQL(conn, sql);

        shf.printResultSetAsHtml(pw, rs, sql);
      }
      System.out.println("Generated file " + file.getAbsolutePath());
    }
    catch (Exception e)
    {
      e.printStackTrace();
      try
      {
        if (rs != null)
          rs.close();
        if (conn != null)
          conn.close();
        if (pw != null)
          pw.close();
      }
      catch (Exception localException1)
      {
      }
    }
    finally
    {
      try
      {
        if (rs != null)
          rs.close();
        if (conn != null)
          conn.close();
        if (pw != null)
          pw.close();
      }
      catch (Exception localException2)
      {
      }
    }
  }
  public static void main1()
  {
	  
	  
	  /*"C:\Users\QA Admin\Documents\bat\temp\sqltohtml\dbConfig.properties"
	  "C:\Users\QA Admin\Documents\bat\temp\sqltohtml\MDM.txt"
	  658036379647531473*/
    
	  SQLHtmlFormatter shf = new SQLHtmlFormatter();
/*
    if ((args != null) && (args.length != 3)) {
      System.err.println("Usage: java SQLHtmlFormatter <dbConfig.properties> <sqls.txt> <cont_id>");
      System.exit(-1);
    }
*/
    String dbConfigFile = "db.properties";
    String sqlFile = "sql.txt";
    String contId = tpartyid;
    Connection conn = null;
    ResultSet rs = null;
    File file = new File("AE_Output_" + contId + ".html");
    PrintWriter pw = null;
    try
    {
      pw = new PrintWriter(file);
      conn = shf.getConnection(dbConfigFile);
      String format = "<STYLE TYPE=\"text/css\"><!-- .boldtable, .boldtable TD, .boldtable TH \t    {\t    \tborder-style: solid; border-width: 1px; border-spacing: 0px; border-color:#999999; font-family:sans-serif;\t    \tfont-size:9pt;\t    \tcolor:blue;\t    \tbackground-color:#e0eee0;\t    \t} .myh1 { font-family:sans-serif;} --></STYLE>";
      pw.println(format);
      String summary = "<h1 class=\"myh1\">Summary for Party: " + contId + "</h1><br/>";
      pw.println(summary);
	  Vector<String> sqls = shf.getSQLsFromFile(sqlFile);
	  
      for (String sql : sqls)
      {
        sql = sql.replace("?", contId);

		System.out.println("Executing SQL " + sql);
        rs = shf.executeSQL(conn, sql);

        shf.printResultSetAsHtml(pw, rs, sql);
      }
      System.out.println("Generated file " + file.getAbsolutePath());
    }
    catch (Exception e)
    {
      e.printStackTrace();
      try
      {
        if (rs != null)
          rs.close();
        if (conn != null)
          conn.close();
        if (pw != null)
          pw.close();
      }
      catch (Exception localException1)
      {
      }
    }
    finally
    {
      try
      {
        if (rs != null)
          rs.close();
        if (conn != null)
          conn.close();
        if (pw != null)
          pw.close();
      }
      catch (Exception localException2)
      {
      }
    }
  }
}